package com.iplatform.file.function.meta.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.Page;

import com.iplatform.file.function.meta.domain.*;
import com.iplatform.file.function.meta.vo.DataColumnVo;
import com.iplatform.file.function.meta.vo.DbConfigVo;
import com.iplatform.file.function.meta.vo.MetaFieldVo;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import javax.sql.DataSource;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.*;

/**
 * JDBC工具
 * @author HENG
 *
 */
@Component
public class JdbcUtil {

	/**
	 * 字符型
	 */
	public static String ROLE_TYPE_STRING = "string";

	/**
	 * 数字型
	 */
	public static String ROLE_TYPE_NUMBER = "number";

	/**
	 * 时间型
	 */
	public static String ROLE_TYPE_DATETIME = "datetime";


	/**
	 * 	驱动名
	 */
	private static String driverClassName;

	@Value("${spring.datasource.driver-class-name}")
	public void setDriverClassName(String driverClassName) {
		JdbcUtil.driverClassName = driverClassName;
	}

	/**
	 * 	获取数据源
	 * @param dbConfigVo
	 * @return
	 */
	public static DataSource getDataSource(DbConfigVo dbConfigVo) {
		DataSource dataSource = null;
		if (dbConfigVo != null) {
			Map<String, String> properties = new HashMap<>(16);
			properties.put("url", dbConfigVo.getUrl());
			properties.put("username", dbConfigVo.getUsername());
			properties.put("password", dbConfigVo.getPassword());
			properties.put("driverClassName", dbConfigVo.getDriverClassName());
			if (StringUtils.isNotEmpty(dbConfigVo.getInitialSize())) {
				properties.put("initialSize", dbConfigVo.getInitialSize());
			}
			if (StringUtils.isNotEmpty(dbConfigVo.getMinIdle())) {
				properties.put("minIdle", dbConfigVo.getMinIdle());
			}
			if (StringUtils.isNotEmpty(dbConfigVo.getMaxActive())) {
				properties.put("maxActive", dbConfigVo.getMaxActive());
			}
			try {
				dataSource = DruidDataSourceFactory.createDataSource(properties);
				((DruidDataSource) dataSource).addConnectionProperty("remarksReporting", "true");
				((DruidDataSource) dataSource).addConnectionProperty("useInformationSchema", "true");
				((DruidDataSource) dataSource).addConnectionProperty("remarks", "true");
				((DruidDataSource) dataSource).setKeepAlive(true);
				((DruidDataSource) dataSource).setTestWhileIdle(true);
				((DruidDataSource) dataSource).setTestOnBorrow(false);
				((DruidDataSource) dataSource).setTestOnReturn(false);
				((DruidDataSource) dataSource).setMaxWait(60000);
				((DruidDataSource) dataSource).setMinEvictableIdleTimeMillis(60000);
				((DruidDataSource) dataSource).setTimeBetweenEvictionRunsMillis(300000);
				((DruidDataSource) dataSource).setMaxEvictableIdleTimeMillis(600000);
//				((DruidDataSource) dataSource).setValidationQuery("SELECT 1");
			} catch (Exception e) {
				e.printStackTrace();
				throw new RuntimeException("数据源创建错误！" + e.toString());
			}
		}
		return dataSource;
	}

	/**
	 * 获取数据库的表
	 * @param connection
	 * @param dbConfigVo
	 * @param name 表名
	 * @param whiteList 表白名单
	 * @return
	 */
	public static List<JSONObject> getTablesFormDb(Connection connection, DbConfigVo dbConfigVo, String name,
												   String comments, String whiteList) {

		List<JSONObject> ret = new ArrayList<>();
		try {
			//getTables的接口的返回，JDBC是将其所有的结果，保存在一个类似table的内存结构中，而其中TABLE_NAME这个名字的字段就是每个表的名字。
			DatabaseMetaData mDBMetaData = connection.getMetaData();
			ResultSet tableRet = mDBMetaData.getTables("iplatform-data", dbConfigVo.getUsername().toUpperCase(),
					StringUtils.isEmpty(name) ? null : "%" + name + "%", new String[] { "TABLE" });
			comments = StringUtils.isEmpty(comments)?"":comments;

			while (tableRet.next()) {
				JSONObject obj = new JSONObject();
				String tableName = tableRet.getString("TABLE_NAME");
				String remarks = tableRet.getString("REMARKS");
				String prefix = tableName.substring(0,
						tableName.indexOf("_") < 0 ? tableName.length() - 1 : tableName.indexOf("_"));
				remarks = StringUtils.isEmpty(remarks)?"":remarks;

				//白名单根据前缀筛选
				if (whiteList.contains(prefix.toUpperCase())) {
					String showName = StringUtils.isEmpty(remarks) ? tableName : remarks;
					if(showName.contains(comments)) {
						obj.put("name", showName);
						obj.put("code", tableName);
						ret.add(obj);
					}
				}
			}

		} catch (SQLException e) {
			throw new RuntimeException("数据库连接错误！" + e.toString());
		} finally {
			closeResource(null, null, connection);
		}
		return ret;
	}

	/**
	 * 通过表名获得表的字段
	 * @param connection
	 * @param tableName
	 * @return
	 */
	public static List<JSONObject> getTableColumns(Connection connection, String tableName) {
		List<JSONObject> columns = new ArrayList<>();
		ResultSet colRet = null;
		try {
			DatabaseMetaData mDBMetaData;
			mDBMetaData = connection.getMetaData();
			String userName = mDBMetaData.getUserName();
			String columnName;
			String columnType;
			String roleType = "string";
			String remarks;
			colRet = mDBMetaData.getColumns("iplatform-data", userName, tableName, "%");
			int i = 1;
			while (colRet.next()) {
				JSONObject column = new JSONObject();
				columnType = colRet.getString("TYPE_NAME");
				columnName = colRet.getString("COLUMN_NAME");
				remarks = StringUtils.isEmpty(colRet.getString("REMARKS")) ? columnName : colRet.getString("REMARKS");
				roleType = columnTypeConverRoleType(columnType);

				column.put("name", remarks);
				column.put("code", columnName);
				column.put("columnType", columnType);
				column.put("roleType", roleType);
				column.put("isOut", Boolean.FALSE);
				column.put("order", "");
				column.put("columnSize", colRet.getInt("COLUMN_SIZE"));
				column.put("digits", colRet.getInt("DECIMAL_DIGITS"));
				column.put("columnOrder", i);
				i++;
				columns.add(column);
			}
		} catch (SQLException e) {
			throw new RuntimeException("数据库连接错误！" + e.toString());
		} finally {
			closeResource(colRet, null, connection);
		}
		return columns;
	}

	/**
	 * 通过表名获得字段
	 * @param
	 * @param tableName
	 * @param
	 * @return
	 * @throws Exception
	 */
	public static List<String> getTableColumnNames(DataSource dataSource, String tableName) throws Exception {
		Connection conn = dataSource.getConnection();
		List<String> columnNames = new ArrayList<>();
		ResultSet colRet = null;
		try {
			DatabaseMetaData mDBMetaData = conn.getMetaData();
			String userName = mDBMetaData.getUserName();
			colRet = mDBMetaData.getColumns(null, userName, tableName, "%");
			while (colRet.next()) {
				columnNames.add(colRet.getString("COLUMN_NAME"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("数据库连接错误！" + e.toString());
		} finally {
			closeResource(colRet, null, conn);
		}
		return columnNames;
	}

	/**
	 * 	通过表名获得字段相关信息
	 * @param dataSource
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	public static List<DataColumn> getTableColumnMapNames(DataSource dataSource, String tableName, String tableId) throws Exception {
		Connection conn = dataSource.getConnection();
		List<DataColumn> dataColumnList = new ArrayList<>();
		ResultSet colRet = null;
		try {
			DatabaseMetaData mDBMetaData = conn.getMetaData();
			String userName = mDBMetaData.getUserName();
			colRet = mDBMetaData.getColumns(null, userName, tableName, "%");
			int i = 1;
			while (colRet.next()) {
				DataColumn dataColumn = new DataColumn();
				DataTable dataTable = new DataTable();
				dataTable.setId(tableId);
				dataColumn.setDataTable(dataTable);
				dataColumn.setCode(colRet.getString("COLUMN_NAME"));
				dataColumn.setName(StringUtils.isEmpty(colRet.getString("REMARKS")) ? colRet.getString("COLUMN_NAME"):
						colRet.getString("REMARKS"));
				dataColumn.setColumnOrder(i + "");
				i++;
				dataColumnList.add(dataColumn);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("数据库连接错误！" + e.toString());
		} finally {
			closeResource(colRet, null, conn);
		}
		return dataColumnList;
	}

	/**
	 * 获得List<DataColumnVo>里的字段集合
	 * @param dataColumnList
	 * @return
	 */
	public static List<String> getDataColumnNames(List<DataColumnVo> dataColumnList) {
		List<String> resultList = new ArrayList<>();
		for(DataColumnVo dataColumn : dataColumnList) {
			if(dataColumn.getIsOut()) {
				resultList.add(dataColumn.getCode());
			}
		}
		return resultList;
	}

	/**
	 * 找到compareList比BaseList多的数据
	 * @param
	 * @param compareList
	 * @return
	 */
	public static List<String> compareList(List<String> baseList, List<String> compareList) {
		if(CollectionUtils.isEmpty(baseList)) {
			return compareList;
		}
		List<String> resultList = new ArrayList<>();
		for(String compare : compareList) {
			if(!baseList.contains(compare)) {
				resultList.add(compare);
			}
		}
		return resultList;
	}


	/**
	 * 只获得List<JSONObject>中的code信息
	 * @param list
	 * @return
	 */
	public static List<String> getArrayFromList(List<JSONObject> list) {
		List<String> codeList = new ArrayList<>();
		for(JSONObject jsonObject : list) {
			String code = jsonObject.get("code").toString();
			codeList.add(code);
		}
		return codeList;
	}


	/**
	 * 	执行sql
	 * @param connection
	 * @param sql
	 * @throws SQLException
	 */
	public static int executeUpdateSql(Connection connection, String sql) {
		PreparedStatement ps = null;
		int num = 0;
		try {
			ps = connection.prepareStatement(sql);
			num = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeResource(null, ps, connection);
		}
		return num;
	}



	/**
	 * 	关闭资源
	 * @param resultSet
	 * @param statement
	 * @param connection
	 */
	public static void closeResource(ResultSet resultSet, Statement statement, Connection connection) {
		// 关闭结果集
		closeResultSet(resultSet);
		// 关闭语句执行者
		closeStatement(statement);
		// 关闭连接
		closeConnection(connection);
	}

	/**
	 * 	关闭连接
	 * @param connection
	 */
	private static void closeConnection(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 	关闭语句执行者
	 * @param statement
	 */
	private static void closeStatement(Statement statement) {
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 	关闭结果集
	 * @param resultSet
	 */
	private static void closeResultSet(ResultSet resultSet) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 	根据sql查询数据结果
	 * @param dataSource
	 * @param querySql
	 * @param maxResultsInPage
	 * @param startPageNo
	 * @param
	 * @param
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static String getDbDataBySql(DataSource dataSource, DbConfig dbConfig,
										String querySql, int maxResultsInPage, int startPageNo, List<MetaFieldVo> titleList) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement state = null;
		List list = new ArrayList();
		List<MetaFieldVo> resultTitleList = new ArrayList<>();

		try {
			conn = dataSource.getConnection();
			long totalCount = getSqlQueryCount(conn, querySql);
			String sql = getSql4QueryPage(querySql, dbConfig.getDriverClassName());
			state = conn.prepareStatement(sql);
			rs = state.executeQuery();

			ResultSetMetaData resultSetMetadata = rs.getMetaData();
			int columnCount = resultSetMetadata.getColumnCount();
			Map<String, MetaFieldVo> titleMap = titleListToMap(titleList);
			//查询出表头，如果编辑进入，表头信息已经存在，不再获取默认的
			initTitleList(resultSetMetadata,columnCount,titleMap,dbConfig,resultTitleList);


			//查询出数据
			while (rs.next()) {
				Map result = new HashMap(16);
				for (int i = 0; i < columnCount; i++) {
					String columnName = resultSetMetadata.getColumnLabel(i + 1);
					if (columnName == null || columnName.length() < 1) {
						columnName = resultSetMetadata.getColumnName(i + 1);
					}
					result.put(columnName, rs.getObject(columnName));
				}
				list.add(result);
			}

			JSONObject ret = new JSONObject();
			JSONObject rmp = new JSONObject();
			Page<Object> page = new Page<Object>(startPageNo, maxResultsInPage, true);
			ret.put("title", resultTitleList);
			page.addAll(list);

			ret.put("page", page);
			ret.put("pageNum",startPageNo);
			ret.put("pageSize",maxResultsInPage);
			ret.put("totalCount", totalCount);
			ret.put("sql",querySql);

			rmp.put("code", 0);
			rmp.put("codeMsg", "");
			rmp.put("status", 200);
			rmp.put("dataList", ret);

			return rmp.toJSONString();
		} catch (SQLException e) {
			e.printStackTrace();
			JSONObject rmp = new JSONObject();
			rmp.put("code", 200);
			rmp.put("codeMsg", e.getMessage());
			rmp.put("status", 500);
			rmp.put("dataList", null);
			return rmp.toJSONString();
		} finally {
			closeResource(rs, state, conn);
		}
	}

	private static void initTitleList(ResultSetMetaData resultSetMetadata, int columnCount, Map<String, MetaFieldVo> titleMap, DbConfig dbConfig, List<MetaFieldVo> resultTitleList) throws SQLException {
		for (int i = 0; i < columnCount; i++) {
			String columnName = resultSetMetadata.getColumnLabel(i + 1);
			if (columnName == null || columnName.length() < 1) {
				columnName = resultSetMetadata.getColumnName(i + 1);
			}

			MetaFieldVo title = titleMap.get(columnName);
			MetaFieldVo metaField = new MetaFieldVo();
			if(title == null) {
				metaField.setId("");
				metaField.setCode(columnName);
				//页面上显示的dataIndex,是为了和拖拽那边保持一致
				metaField.setDataIndex(columnName);
				metaField.setSourceFieldId("");
				metaField.setName(columnName);
				if(dbConfig.getDriverClassName().contains("oracle")) {
					metaField.setColumnType("varchar2");
				}else if(dbConfig.getDriverClassName().contains("mysql")) {
					metaField.setColumnType("varchar");
				}
				metaField.setFieldSize("255");
				metaField.setDigits("0");
				metaField.setNullAble("Y");
				metaField.setForeignKeyField("");
				metaField.setOrder(i+1);
				metaField.setAlias("");
				metaField.setTableCode("");
				metaField.setIsChecked("");
				metaField.setRoleType(JdbcUtil.ROLE_TYPE_STRING);
			}else {
				metaField = title;
			}
			resultTitleList.add(metaField);
		}
	}

	/**
	 * titleList转换为map类型的
	 * @param titleList
	 * @return
	 */
	public static Map<String, MetaFieldVo> titleListToMap(List<MetaFieldVo> titleList) {
		Map<String, MetaFieldVo> map = new HashMap<>(16);
		for(MetaFieldVo metaFieldVo : titleList) {
			map.put(metaFieldVo.getDataIndex(), metaFieldVo);
		}
		return map;
	}


	/**
	 * 根据拖拽表查询数据
	 * @param dataSource
	 * @param dbConfig
	 * @param querySql
	 * @param maxResultsInPage
	 * @param startPageNo
	 * @param titleList
	 * @return
	 */
	public static JSONObject getDbDataBySet(DataSource dataSource, DbConfig dbConfig,
											String querySql, int maxResultsInPage, int startPageNo,
											List<DataColumnVo> titleList) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement state = null;
		try {
			conn = dataSource.getConnection();
			long totalCount = getSqlQueryCount(conn, querySql);
			// 如果记录总数小于1则返回空的Page
			if (totalCount < 1) {
				JSONObject rmp = new JSONObject();
				JSONObject ret = new JSONObject();
				ret.put("title", titleList);
				ret.put("page", new Page<Object>(startPageNo, maxResultsInPage, true));
				ret.put("pageNum",startPageNo);
				ret.put("pageSize",maxResultsInPage);
				ret.put("totalCount",totalCount);
				ret.put("sql",querySql);

				rmp.put("code", 0);
				rmp.put("codeMsg", "");
				rmp.put("status", 200);
				rmp.put("dataList", ret);
				return rmp;
			}

			//为加快查询速度，限制查询10条语句
			String sql = getSql4QueryPage(querySql, dbConfig.getDriverClassName());
			List<Map<String, Object>> list = new ArrayList<>();

			state = conn.prepareStatement(sql);
			rs = state.executeQuery();
			ResultSetMetaData resultSetMetadata = rs.getMetaData();
			int columnCount = resultSetMetadata.getColumnCount();

			//查询数据
			while (rs.next()) {
				Map<String, Object> result = new HashMap<>(16);
				for (int i = 0; i < columnCount; i++) {
					String columnName = resultSetMetadata.getColumnLabel(i + 1);
					if (columnName == null || columnName.length() < 1) {
						columnName = resultSetMetadata.getColumnName(i + 1);
					}
					result.put(columnName, rs.getObject(columnName));
				}
				list.add(result);
			}

			JSONObject rmp = new JSONObject();
			JSONObject ret = new JSONObject();
			ret.put("title", titleList);
			Page<Object> page = new Page<Object>(startPageNo, maxResultsInPage, true);
			page.addAll(list);
			ret.put("page", page);
			ret.put("pageNum",startPageNo);
			ret.put("pageSize",maxResultsInPage);
			ret.put("totalCount",totalCount);
			ret.put("sql",querySql);
			rmp.put("code", 0);
			rmp.put("codeMsg", "");
			rmp.put("status", 200);
			rmp.put("dataList", ret);
			return rmp;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally {
			try {
				if (rs!=null){
					rs.close();
				}
				if (state!=null){
					state.close();
				}
				if (conn!=null){
					conn.close();
				}
			} catch (SQLException throwables) {
				throwables.printStackTrace();
			}
		}
	}


	/**
	 * 	查询语句限制10条
	 * @param sql4Query
	 * @param dbType
	 * @return
	 */
	public static String getSql4QueryPage(final String sql4Query, String dbType) {
		String sql = "";
		if(dbType.contains("oracle")) {
			sql = "select * from (" + sql4Query + ") t where rownum<=10";
		}else if(dbType.contains("mysql")) {
			sql = "select * from (" + sql4Query + ") t limit 10";
		}
		return sql;
	}

	/**
	 * 查询sql的结果列数
	 * @param
	 * @return
	 */
	public static long getSqlQueryCount(Connection connection, String sql) {
		StringBuffer result = new StringBuffer(" select count(0) from ( ");
		result.append(sql);
		result.append(" ) pageCount");

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = connection.prepareStatement(result.toString());
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getLong(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 从SQL中获取字段
	 * @param sql
	 * @return
	 */
	public static List<String> getFieldsBySql(String sql) {
		String sqlStr = sql.substring(sql.indexOf("select")+6, sql.lastIndexOf("from"));
		String[] sqlSplit = sqlStr.trim().split("\" ,");
		List<String> columnArray = new ArrayList<String>();
		for (String str : sqlSplit) {
			String sub = str.substring(0, str.lastIndexOf("as"));
			columnArray.add(sub.trim());
		}
		return columnArray;
	}

	/**
	 * 获取V表表名，V加上时间
	 * @param
	 * @return
	 */
	public static String getTableName() {
		Date now = new Date();
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
		String tableName = "DM_" + dateFormat.format(now);
		return tableName;
	}

	/**
	 * 	根据字段型转换成role类型
	 * @return
	 */
	public static String columnTypeConverRoleType(String columnType) {
		if(StringUtils.isEmpty(columnType)) {
			return "";
		}
		String roleType = "";
		columnType = columnType.toUpperCase();
		if(driverClassName.contains("oracle")) {
			if (columnType.contains("NUMBER")) {
				roleType = JdbcUtil.ROLE_TYPE_NUMBER;
			} else if (columnType.contains("DATE")||columnType.contains("TIMESTAMP")) {
				roleType = JdbcUtil.ROLE_TYPE_DATETIME;
			}else if(columnType.contains("VARCHAR2")||columnType.contains("CHAR")||columnType.contains("LONG")
					||columnType.contains("BLOB")||columnType.contains("CLOB")) {
				roleType = JdbcUtil.ROLE_TYPE_STRING;
			}
		}else if(driverClassName.contains("mysql")) {
			if (columnType.contains("INT")||columnType.contains("FLOART")||columnType.contains("DOUBLE")||columnType.contains("DECIMAL")) {
				roleType = JdbcUtil.ROLE_TYPE_NUMBER;
			} else if (columnType.contains("DATE")||columnType.contains("TIMESTAMP")) {
				roleType = JdbcUtil.ROLE_TYPE_DATETIME;
			}else if(columnType.contains("VARCHAR")||columnType.contains("CHAR")||columnType.contains("BLOB")||columnType.contains("TEXT")) {
				roleType = JdbcUtil.ROLE_TYPE_STRING;
			}
		}
		return roleType;
	}

	/**
	 * 根据role类型决定字段的类型
	 * @param roleType
	 * @param
	 * @return
	 */
	public static String roleTypeConverColumnType(String roleType) {
		if(StringUtils.isEmpty(roleType)) {
			return "";
		}
		String columnType = "";
		roleType = roleType.toLowerCase();
		if(driverClassName.contains("oracle")) {
			if (roleType.equals(JdbcUtil.ROLE_TYPE_STRING)) {
				columnType = "VARCHAR2";
			} else if (roleType.equals(JdbcUtil.ROLE_TYPE_DATETIME)) {
				columnType = "TIMESTAMP";
			}else if(roleType.equals(JdbcUtil.ROLE_TYPE_NUMBER)) {
				columnType = "NUMBER";
			}
		}else if(driverClassName.contains("mysql")) {
			if (roleType.equals(JdbcUtil.ROLE_TYPE_STRING)) {
				columnType = "VARCHAR";
			} else if (roleType.equals(JdbcUtil.ROLE_TYPE_DATETIME)) {
				columnType = "DATE";
			}else if(roleType.equals(JdbcUtil.ROLE_TYPE_NUMBER)) {
				columnType = "DECIMAL";
			}
		}
		return columnType;
	}

	/**
	 * 	根据字段类型查询出默认数据库大小和精度
	 * @param roleType
	 * @param
	 * @return
	 */
	public static Map<String, String> getDefaultByRoleType(String roleType) {
		if(StringUtils.isEmpty(roleType)) {
			return null;
		}
		String columnType = "";
		String columnSize = "255";
		String digits = "2";
		roleType = roleType.toUpperCase();
		if(driverClassName.contains("oracle")) {
			if (roleType.equals(JdbcUtil.ROLE_TYPE_STRING)) {
				columnType = "VARCHAR2";
				columnSize = "255";
			} else if (roleType.equals(JdbcUtil.ROLE_TYPE_DATETIME)) {
				columnType = "TIMESTAMP";
				columnSize = "6";
			}else if(roleType.equals(JdbcUtil.ROLE_TYPE_NUMBER)) {
				columnType = "NUMBER";
			}
		}else if(driverClassName.contains("mysql")) {
			if (roleType.equals(JdbcUtil.ROLE_TYPE_STRING)) {
				columnType = "VARCHAR";
				columnSize = "255";
			} else if (roleType.equals(JdbcUtil.ROLE_TYPE_DATETIME)) {
				columnType = "DATE";
			}else if(roleType.equals(JdbcUtil.ROLE_TYPE_NUMBER)) {
				columnType = "DECIMAL";
				columnSize = "10";
				digits = "2";
			}
		}
		Map<String, String> map = new HashMap<>(16);
		map.put("columnType", columnType);
		map.put("columnSize", columnSize);
		map.put("digits", digits);
		return map;
	}

	/**
	 * 	获取创建表语句中的字段信息，dataIndex1 VARCHAR2 (64) DEFAULT NULL,
	 * @param fieldName
	 * @param fieldType
	 * @param fieldSize
	 * @param digits
	 * @return
	 */
	public static String getFieldForCreateTable(String fieldName, String fieldType,
												String fieldSize, String digits) {
		fieldType = fieldType.toUpperCase();
		fieldName += " " ;
		if(driverClassName.contains("oracle")) {
			if("VARCHAR".equals(fieldType)||"CHAR".equals(fieldType)
					||"TIMESTAMP".equals(fieldType)||"VARCHAR2".equals(fieldType)
					||"LONG".equals(fieldType)) {
				return fieldName + fieldType + "(" + fieldSize + ") ";
			}else if("NUMBER".equals(fieldType)) {
				return fieldName + fieldType + "(" + fieldSize + "," + digits + ") ";
			}else if("NVARCHAR2".equals(fieldType)) {
				return fieldName + "VARCHAR2 " + "(" + fieldSize + ") ";
			}else if("TIMESTAMP(6)".equals(fieldType)||"CLOB".equals(fieldType)||"BLOB".equals(fieldType)||"DATE".equals(fieldType)) {
				return fieldName + fieldType + " ";
			}
		}else if(driverClassName.contains("mysql")) {
			if("INT".equals(fieldType)||"VARCHAR".equals(fieldType)
					||"TIMESTAMP".equals(fieldType)||"LONG".equals(fieldType)) {
				return fieldName + fieldType + "(" + fieldSize + ") ";
			}else if("DATE".equals(fieldType)||"DATETIME".equals(fieldType)||"TIMESTAMP".equals(fieldType)||"TEXT".equals(fieldType)) {
				//不需要长度的
				return fieldName + fieldType + " ";
			}else if("FLOART".equals(fieldType)||"DOUBLE".equals(fieldType)||"DECIMAL".equals(fieldType)) {
				//需要精度的
				return fieldName + fieldType + "(" + fieldSize + "," + digits + ") ";
			}
		}
		return fieldName;
	}


	/**
	 * 根据表名和字段名查询字段详情
	 * @param
	 * @param
	 * @param
	 * @return
	 * @throws SQLException
	 */
	public static Map<String, Object> getColumnDetailByTableAndColumn(DataSource dataSource, String tableCode,
																	  String columnCode) {
		Connection conn = null;
		Map<String, Object> formMap = new HashMap<>(16);
		try {
			conn = dataSource.getConnection();
			DatabaseMetaData mDBMetaData = conn.getMetaData();
			ResultSet colRet = mDBMetaData.getColumns(null, mDBMetaData.getUserName(), tableCode, columnCode);
			while (colRet.next()) {
				if (columnCode.equals(colRet.getString("COLUMN_NAME"))) {
					formMap.put("tableCode", tableCode);
					formMap.put("columnCode", colRet.getString("COLUMN_NAME"));
					formMap.put("comments", colRet.getString("REMARKS"));
					formMap.put("columnType", "NVARCHAR2".equals(colRet.getString("TYPE_NAME"))?"VARCHAR2":colRet.getString("TYPE_NAME"));
					formMap.put("columnSize", "NVARCHAR2".equals(colRet.getString("TYPE_NAME"))?colRet.getInt("COLUMN_SIZE")*2 : colRet.getInt("COLUMN_SIZE"));
					formMap.put("digits", colRet.getInt("DECIMAL_DIGITS"));
					formMap.put("nullable", colRet.getString("NULLABLE"));
					formMap.put("columnDef", colRet.getString("COLUMN_DEF"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn);
		}
		return formMap;
	}

	/**
	 * object转字符串
	 * @param object
	 * @return
	 */
	public static String strToString(Object object) {
		if(object == null) {
			return "";
		}
		return object.toString();
	}

	/**
	 *
	 * 表字段的命名，字段名称取源表字段的名称，如果字段名称有重复，则加上序号2，3等等
	 * @param codeMap
	 * @param nowFieldName
	 * @param lastFieldName
	 * @return
	 */
	public static String transCodeName(Map<String, List<String>> codeMap, String nowFieldName, String lastFieldName) {
		List<String> codeList = codeMap.get(nowFieldName);
		if(CollectionUtils.isEmpty(codeList)) {
			codeList = new ArrayList<>();
			codeList.add(nowFieldName);
			codeMap.put(nowFieldName, codeList);
			lastFieldName = nowFieldName;
		}else {
			int size = codeList.size() + 1;
			codeList.add(nowFieldName + size);
			codeMap.put(nowFieldName, codeList);
			lastFieldName = nowFieldName + size;
		}
		return lastFieldName;
	}

	/**
	 * 将不同类型的表分好类
	 * @param
	 * @param 
	 * @param synTables
	 */
	public static void getSynTables(EtlTableHistoryBean etlTableHis, SynTables synTables) {
		etlTableHis.setSynSql("");
		switch(etlTableHis.getTableType().toUpperCase()) {
			case "V":
				Set<EtlTableHistoryBean> dimSet = synTables.getDimSet();
				dimSet.add(etlTableHis);
				break;
			case "DIM":
				Set<EtlTableHistoryBean> dimmSet = synTables.getDimSet();
				dimmSet.add(etlTableHis);
				break;
			case "DM":
				Set<EtlTableHistoryBean> dmSet = synTables.getDmSet();
				dmSet.add(etlTableHis);
				break;
			case "ADS":
				Set<EtlTableHistoryBean> adsSet = synTables.getAdsSet();
				adsSet.add(etlTableHis);
				break;
			case "DWT":
				Set<EtlTableHistoryBean> dwtSet = synTables.getDwtSet();
				dwtSet.add(etlTableHis);
				break;
			case "DWD":
				Set<EtlTableHistoryBean> dwdSet = synTables.getDwdSet();
				dwdSet.add(etlTableHis);
				break;
			case "ODSSS":
				Set<EtlTableHistoryBean> odsssSet = synTables.getOdsssSet();
				odsssSet.add(etlTableHis);
				break;
			case "ODS":
				Set<EtlTableHistoryBean> odsSet = synTables.getOdsSet();
				odsSet.add(etlTableHis);
				break;
			default :
				break;
		}

	}




}
